﻿
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'RethrowError')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].RethrowError AS RETURN')
END
GO

ALTER PROCEDURE RethrowError AS
    /* Return if there is no error information to retrieve. */
    IF ERROR_NUMBER() IS NULL
        RETURN;

    DECLARE
        @ErrorMessage    NVARCHAR(4000),
        @ErrorNumber     INT,
        @ErrorSeverity   INT,
        @ErrorState      INT,
        @ErrorLine       INT,
        @ErrorProcedure  NVARCHAR(200); 

    /* Assign variables to error-handling functions that
       capture information for RAISERROR. */

    SELECT
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorLine = ERROR_LINE(),
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-'); 

    /* Building the message string that will contain original
       error information. */

    SELECT @ErrorMessage = 
        N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 
         'Message: '+ ERROR_MESSAGE(); 

    /* Raise an error: msg_str parameter of RAISERROR will contain
	   the original error information. */

    RAISERROR(@ErrorMessage, @ErrorSeverity, 1,
        @ErrorNumber,    /* parameter: original error number. */
        @ErrorSeverity,  /* parameter: original error severity. */
        @ErrorState,     /* parameter: original error state. */
        @ErrorProcedure, /* parameter: original error procedure name. */
        @ErrorLine       /* parameter: original error line number. */
        );

GO

----------------------------------------------------------------
-- [dbo].[dnc_Advertisement] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Insertdnc_Advertisement')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Insertdnc_Advertisement] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Insertdnc_Advertisement]
    @active bit,
	@advertiserID int,
	@alternateText varchar(100) = NULL,
	@endDate datetime = NULL,
	@iD int OUT,
	@imageID int,
	@name varchar(100),
	@startDate datetime = NULL,
	@uRL varchar(8000)
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[dnc_Advertisement] ([Active], [AdvertiserID], [AlternateText], [EndDate], [ImageID], [Name], [startDate], [URL])
	VALUES (@active, @advertiserID, @alternateText, @endDate, @imageID, @name, @startDate, @uRL)
    SET @iD = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Updatednc_Advertisement')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Updatednc_Advertisement] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Updatednc_Advertisement]
    @active bit,
	@advertiserID int,
	@alternateText varchar(100) = NULL,
	@endDate datetime = NULL,
	@iD int,
	@imageID int,
	@name varchar(100),
	@startDate datetime = NULL,
	@uRL varchar(8000)
AS
BEGIN

	--The [dbo].[dnc_Advertisement] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[dnc_Advertisement] 
	SET [Active] = @active, [AdvertiserID] = @advertiserID, [AlternateText] = @alternateText, [EndDate] = @endDate, [ImageID] = @imageID, [Name] = @name, [startDate] = @startDate, [URL] = @uRL
	WHERE [ID]=@iD

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Deletednc_Advertisement')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Deletednc_Advertisement] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Deletednc_Advertisement]
	 @iD int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[dnc_Advertisement]
	WHERE [ID]=@iD
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromdnc_Advertisement')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromdnc_Advertisement] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromdnc_Advertisement]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[dnc_Advertisement].[Active] AS 'Active',
	[dnc_Advertisement].[AdvertiserID] AS 'AdvertiserID',
	[dnc_Advertisement].[AlternateText] AS 'AlternateText',
	[dnc_Advertisement].[EndDate] AS 'EndDate',
	[dnc_Advertisement].[ID] AS 'ID',
	[dnc_Advertisement].[ImageID] AS 'ImageID',
	[dnc_Advertisement].[Name] AS 'Name',
	[dnc_Advertisement].[startDate] AS 'startDate',
	[dnc_Advertisement].[URL] AS 'URL'
FROM [dbo].[dnc_Advertisement] [dnc_Advertisement]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllPendingFromdnc_Advertisement')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllPendingFromdnc_Advertisement] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllPendingFromdnc_Advertisement]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[dnc_Advertisement].[Active] AS 'Active',
	[dnc_Advertisement].[AdvertiserID] AS 'AdvertiserID',
	[dnc_Advertisement].[AlternateText] AS 'AlternateText',
	[dnc_Advertisement].[EndDate] AS 'EndDate',
	[dnc_Advertisement].[ID] AS 'ID',
	[dnc_Advertisement].[ImageID] AS 'ImageID',
	[dnc_Advertisement].[Name] AS 'Name',
	[dnc_Advertisement].[startDate] AS 'startDate',
	[dnc_Advertisement].[URL] AS 'URL'
FROM [dbo].[dnc_Advertisement] [dnc_Advertisement]
WHERE [Active]=0

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Getdnc_AdvertisementByAdvertiserID')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Getdnc_AdvertisementByAdvertiserID] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Getdnc_AdvertisementByAdvertiserID] 
	@iD int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[dnc_Advertisement].[Active] AS 'Active',
	[dnc_Advertisement].[AdvertiserID] AS 'AdvertiserID',
	[dnc_Advertisement].[AlternateText] AS 'AlternateText',
	[dnc_Advertisement].[EndDate] AS 'EndDate',
	[dnc_Advertisement].[ID] AS 'ID',
	[dnc_Advertisement].[ImageID] AS 'ImageID',
	[dnc_Advertisement].[Name] AS 'Name',
	[dnc_Advertisement].[startDate] AS 'startDate',
	[dnc_Advertisement].[URL] AS 'URL'
	FROM [dbo].[dnc_Advertisement] [dnc_Advertisement]
	WHERE [AdvertiserID]=@iD

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Getdnc_AdvertisementByID')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Getdnc_AdvertisementByID] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Getdnc_AdvertisementByID] 
	@iD int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[dnc_Advertisement].[Active] AS 'Active',
	[dnc_Advertisement].[AdvertiserID] AS 'AdvertiserID',
	[dnc_Advertisement].[AlternateText] AS 'AlternateText',
	[dnc_Advertisement].[EndDate] AS 'EndDate',
	[dnc_Advertisement].[ID] AS 'ID',
	[dnc_Advertisement].[ImageID] AS 'ImageID',
	[dnc_Advertisement].[Name] AS 'Name',
	[dnc_Advertisement].[startDate] AS 'startDate',
	[dnc_Advertisement].[URL] AS 'URL'
	FROM [dbo].[dnc_Advertisement] [dnc_Advertisement]
	WHERE [ID]=@iD

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetNextAdvertisement')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetNextAdvertisement] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetNextAdvertisement]
   	@zoneName varchar(255)
AS
BEGIN

	--The [dbo].[dnc_Advertiser] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	
	SELECT Advertisement.ID, Advertisement.Name, Advertisement.ImageID, Advertisement.EndDate, Advertisement.StartDate, Advertisement.AdvertiserID, Advertisement.Active, Advertisement.AlternateText, Advertisement.URL, COUNT(Advertisement.ID) as ImpressionCount
	FROM dnc_Advertisement advertisement
	LEFT OUTER JOIN dnc_Impression impression 
		ON advertisement.ID = impression.AdvertisementID AND impression.Impression >=  DATEADD(day, -2, CURRENT_TIMESTAMP)
	JOIN dnc_AdvertisementZone adZone
		ON adZone.AdvertisementID = advertisement.ID
	JOIN dnc_Zone zone
		ON zone.ID = adZone.ZoneID
	WHERE Active = 1 
		AND endDate >= CURRENT_TIMESTAMP 
		AND startDate <= CURRENT_TIMESTAMP
		
		AND zone.Name = @zoneName
		
	GROUP BY Advertisement.ID, Advertisement.Name, Advertisement.ImageID, Advertisement.EndDate, Advertisement.StartDate, Advertisement.AdvertiserID, Advertisement.Active, Advertisement.AlternateText, Advertisement.URL
	ORDER BY ImpressionCount ASC
	
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[dnc_Advertiser] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Insertdnc_Advertiser')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Insertdnc_Advertiser] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Insertdnc_Advertiser]
    @iD int OUT,
	@name varchar(100),
	@userId varchar(255) = NULL
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[dnc_Advertiser] ([Name], [UserId])
	VALUES (@name, @userId)
    SET @iD = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Updatednc_Advertiser')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Updatednc_Advertiser] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Updatednc_Advertiser]
    @iD int,
	@name varchar(100),
	@userId varchar(255) = NULL
AS
BEGIN

	--The [dbo].[dnc_Advertiser] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[dnc_Advertiser] 
	SET [Name] = @name, [UserId] = @userId
	WHERE [ID]=@iD

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Deletednc_Advertiser')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Deletednc_Advertiser] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Deletednc_Advertiser]
	 @iD int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[dnc_Advertiser]
	WHERE [ID]=@iD
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromdnc_Advertiser')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromdnc_Advertiser] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromdnc_Advertiser]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[dnc_Advertiser].[ID] AS 'ID',
	[dnc_Advertiser].[Name] AS 'Name',
	[dnc_Advertiser].[UserId] AS 'UserId'
FROM [dbo].[dnc_Advertiser] [dnc_Advertiser]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Getdnc_AdvertiserByID')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Getdnc_AdvertiserByID] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Getdnc_AdvertiserByID] 
	@iD int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[dnc_Advertiser].[ID] AS 'ID',
	[dnc_Advertiser].[Name] AS 'Name',
	[dnc_Advertiser].[UserId] AS 'UserId'
	FROM [dbo].[dnc_Advertiser] [dnc_Advertiser]
	WHERE [ID]=@iD

	SET NOCOUNT OFF
END

GO


--The GetByFK stored procedure cannot be created because the [dbo].[dnc_Advertiser] table doesn't have at least one foreign key column or the foreign keys are also primary keys.

----------------------------------------------------------------
-- [dbo].[dnc_ClickThrough] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Insertdnc_ClickThrough')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Insertdnc_ClickThrough] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Insertdnc_ClickThrough]
    @advertisementID int,
	@clickThrough datetime
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[dnc_ClickThrough] ([AdvertisementID], [ClickThrough])
	VALUES (@advertisementID, @clickThrough)
    
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO


GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromdnc_ClickThrough')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromdnc_ClickThrough] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromdnc_ClickThrough]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[dnc_ClickThrough].[AdvertisementID] AS 'AdvertisementID',
	[dnc_ClickThrough].[ClickThrough] AS 'ClickThrough'
FROM [dbo].[dnc_ClickThrough] [dnc_ClickThrough]

	SET NOCOUNT OFF
END

GO


--The GetByPK stored procedure cannot be created because the [dbo].[dnc_ClickThrough] table doesn't have at least one primary key column.

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Getdnc_ClickThroughByAdvertisementID')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Getdnc_ClickThroughByAdvertisementID] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Getdnc_ClickThroughByAdvertisementID] 
	@advertisementID int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[dnc_ClickThrough].[AdvertisementID] AS 'AdvertisementID',
	[dnc_ClickThrough].[ClickThrough] AS 'ClickThrough'
	FROM [dbo].[dnc_ClickThrough] [dnc_ClickThrough]
	WHERE [AdvertisementID]=@advertisementID

	SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[dnc_Image] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Insertdnc_Image')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Insertdnc_Image] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Insertdnc_Image]
    @contentType varchar(100),
	@height decimal(7,2),
	@iD int OUT,
	@image varbinary(MAX),
	@name varchar(100),
	@width decimal(7,2)
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[dnc_Image] ([ContentType], [Height], [Image], [Name], [Width])
	VALUES (@contentType, @height, @image, @name, @width)
    SET @iD = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Updatednc_Image')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Updatednc_Image] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Updatednc_Image]
    @contentType varchar(100),
	@height decimal(5,2),
	@iD int,
	@image varbinary(MAX),
	@name varchar(100),
	@width decimal(5,2)
AS
BEGIN

	--The [dbo].[dnc_Image] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[dnc_Image] 
	SET [ContentType] = @contentType, [Height] = @height, [Image] = @image, [Name] = @name, [Width] = @width
	WHERE [ID]=@iD

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Deletednc_Image')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Deletednc_Image] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Deletednc_Image]
	 @iD int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[dnc_Image]
	WHERE [ID]=@iD
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromdnc_Image')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromdnc_Image] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromdnc_Image]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[dnc_Image].[ContentType] AS 'ContentType',
	[dnc_Image].[Height] AS 'Height',
	[dnc_Image].[ID] AS 'ID',
	[dnc_Image].[Image] AS 'Image',
	[dnc_Image].[Name] AS 'Name',
	[dnc_Image].[Width] AS 'Width'
FROM [dbo].[dnc_Image] [dnc_Image]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Getdnc_ImageByID')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Getdnc_ImageByID] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Getdnc_ImageByID] 
	@iD int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[dnc_Image].[ContentType] AS 'ContentType',
	[dnc_Image].[Height] AS 'Height',
	[dnc_Image].[ID] AS 'ID',
	[dnc_Image].[Image] AS 'Image',
	[dnc_Image].[Name] AS 'Name',
	[dnc_Image].[Width] AS 'Width'
	FROM [dbo].[dnc_Image] [dnc_Image]
	WHERE [ID]=@iD

	SET NOCOUNT OFF
END

GO


--The GetByFK stored procedure cannot be created because the [dbo].[dnc_Image] table doesn't have at least one foreign key column or the foreign keys are also primary keys.

----------------------------------------------------------------
-- [dbo].[dnc_Impression] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Insertdnc_Impression')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Insertdnc_Impression] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Insertdnc_Impression]
    @advertisementID int,
	@impression datetime
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[dnc_Impression] ([AdvertisementID], [Impression])
	VALUES (@advertisementID, @impression)
    
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO


GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromdnc_Impression')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromdnc_Impression] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromdnc_Impression]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[dnc_Impression].[AdvertisementID] AS 'AdvertisementID',
	[dnc_Impression].[Impression] AS 'Impression'
FROM [dbo].[dnc_Impression] [dnc_Impression]

	SET NOCOUNT OFF
END

GO


--The GetByPK stored procedure cannot be created because the [dbo].[dnc_Impression] table doesn't have at least one primary key column.

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Getdnc_ImpressionByAdvertisementID')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Getdnc_ImpressionByAdvertisementID] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Getdnc_ImpressionByAdvertisementID] 
	@advertisementID int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[dnc_Impression].[AdvertisementID] AS 'AdvertisementID',
	[dnc_Impression].[Impression] AS 'Impression'
	FROM [dbo].[dnc_Impression] [dnc_Impression]
	WHERE [AdvertisementID]=@advertisementID

	SET NOCOUNT OFF
END

GO



IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'RethrowError')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].RethrowError AS RETURN')
END
GO

ALTER PROCEDURE RethrowError AS
    /* Return if there is no error information to retrieve. */
    IF ERROR_NUMBER() IS NULL
        RETURN;

    DECLARE
        @ErrorMessage    NVARCHAR(4000),
        @ErrorNumber     INT,
        @ErrorSeverity   INT,
        @ErrorState      INT,
        @ErrorLine       INT,
        @ErrorProcedure  NVARCHAR(200); 

    /* Assign variables to error-handling functions that
       capture information for RAISERROR. */

    SELECT
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorLine = ERROR_LINE(),
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-'); 

    /* Building the message string that will contain original
       error information. */

    SELECT @ErrorMessage = 
        N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 
         'Message: '+ ERROR_MESSAGE(); 

    /* Raise an error: msg_str parameter of RAISERROR will contain
	   the original error information. */

    RAISERROR(@ErrorMessage, @ErrorSeverity, 1,
        @ErrorNumber,    /* parameter: original error number. */
        @ErrorSeverity,  /* parameter: original error severity. */
        @ErrorState,     /* parameter: original error state. */
        @ErrorProcedure, /* parameter: original error procedure name. */
        @ErrorLine       /* parameter: original error line number. */
        );

GO

----------------------------------------------------------------
-- [dbo].[dnc_Advertiser] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Insertdnc_Advertiser')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Insertdnc_Advertiser] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Insertdnc_Advertiser]
    @iD int OUT,
	@name varchar(100),
	@userId varchar(255) = NULL
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[dnc_Advertiser] ([Name], [UserId])
	VALUES (@name, @userId)
    SET @iD = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Updatednc_Advertiser')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Updatednc_Advertiser] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Updatednc_Advertiser]
    @iD int,
	@name varchar(100),
	@userId varchar(255) = NULL
AS
BEGIN

	--The [dbo].[dnc_Advertiser] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[dnc_Advertiser] 
	SET [Name] = @name, [UserId] = @userId
	WHERE [ID]=@iD

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Deletednc_Advertiser')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Deletednc_Advertiser] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Deletednc_Advertiser]
	 @iD int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[dnc_Advertiser]
	WHERE [ID]=@iD
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromdnc_Advertiser')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromdnc_Advertiser] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromdnc_Advertiser]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[dnc_Advertiser].[ID] AS 'ID',
	[dnc_Advertiser].[Name] AS 'Name',
	[dnc_Advertiser].[UserId] AS 'UserId'
FROM [dbo].[dnc_Advertiser] [dnc_Advertiser]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Getdnc_AdvertiserByID')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Getdnc_AdvertiserByID] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Getdnc_AdvertiserByID] 
	@iD int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[dnc_Advertiser].[ID] AS 'ID',
	[dnc_Advertiser].[Name] AS 'Name',
	[dnc_Advertiser].[UserId] AS 'UserId'
	FROM [dbo].[dnc_Advertiser] [dnc_Advertiser]
	WHERE [ID]=@iD

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Getdnc_AdvertiserByUserID')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Getdnc_AdvertiserByUserID] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Getdnc_AdvertiserByUserID] 
	@userId varchar(255)
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[dnc_Advertiser].[ID] AS 'ID',
	[dnc_Advertiser].[Name] AS 'Name',
	[dnc_Advertiser].[UserId] AS 'UserId'
	FROM [dbo].[dnc_Advertiser] [dnc_Advertiser]
	WHERE [UserId]=@userId

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetImpressionsByAdvertisementID')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetImpressionsByAdvertisementID] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetImpressionsByAdvertisementID] 
	@advertisementId int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT COUNT(i.AdvertisementID) as Number
FROM dnc_Impression i
WHERE i.AdvertisementID=@advertisementID

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetClickThroughsByAdvertisementID')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetClickThroughsByAdvertisementID] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetClickThroughsByAdvertisementID] 
	@advertisementId int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT COUNT(i.AdvertisementID) as Number
FROM dnc_ClickThrough i
WHERE i.AdvertisementID=@advertisementID

	SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[dnc_AdvertisementZone] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'Insertdnc_AdvertisementZone')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[Insertdnc_AdvertisementZone] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[Insertdnc_AdvertisementZone]
    @advertisementID int,
	@zoneID int
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[dnc_AdvertisementZone] ([AdvertisementID], [ZoneID])
	VALUES (@advertisementID, @zoneID)
    
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromdnc_AdvertisementZone')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromdnc_AdvertisementZone] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromdnc_AdvertisementZone]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[dnc_AdvertisementZone].[AdvertisementID] AS 'AdvertisementID',
	[dnc_AdvertisementZone].[ZoneID] AS 'ZoneID'
FROM [dbo].[dnc_AdvertisementZone] [dnc_AdvertisementZone]

	SET NOCOUNT OFF
END

GO
